package persistence.sale.saleDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import client.exceptions.BadClientException;
import dbExceptions.NotSaleException;
import persistence.DAO.BadEmployeeException;
import persistence.DAO.DAOHelper;
import persistence.client.clientDAO.ClientDAO;
import persistence.employee.employeeDAO.EmployeeDAO;
import business.article.entities.Article;
import business.article.entities.Category;
import business.article.entities.Combo;
import business.article.entities.Ingredient;
import business.client.entities.Client;
import business.employee.entities.Employee;
import business.sale.entities.Order;
import business.sale.entities.Sale;


public class SaleDAO {

	/**
	 * Singleton implementation begin
	 */

	private static SaleDAO instance = null;

	private SaleDAO() {
	}

	public static SaleDAO getInstance() {
		if (instance == null)
			instance = new SaleDAO();
		return instance;
	}

	/**
	 * Singleton implementation end
	 */

	public Sale[] getOpenSales() {//table key > 0
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			String sql= "SELECT " +//
				  "inner_key," +//1
				  "id_employee," +//2
				  "id_client," +//3
				  "open_date," +//4
				  "close_date," +//5
				  "address," + //6
				  "tel," +//7
				  "is_open," +//8
				  "total," +//9
				  "table_key"+//11
				  " FROM Sale WHERE is_open=1 AND table_key>0";
			ResultSet r=con.createStatement().executeQuery(sql);
				  ArrayList<Sale> articulos=new ArrayList<Sale>();
				  
				  while(r.next()){
					Sale s=new Sale(r.getInt(1),
							  r.getInt(10),
							  getEmployee(r.getInt(2)),
							  getClient(r.getInt(3)),
							  new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(r.getString(4)),
							  new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(r.getString(5)),
							  null,
							  r.getString(6),
							  r.getString(7),
							  r.getBoolean(8),
							  r.getDouble(9));
					  s.setOrders(getOrder(s));
					  articulos.add(s); }
				  
				  Sale[] ingr=new Sale[articulos.size()];
				  articulos.toArray(ingr);
				  return ingr; }
		catch (SQLException e) { e.printStackTrace();
				  } catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					if(con!=null)
						try {
							con.close();
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}	
			  }
		return null;
	}
	
	private Employee getEmployee(int id){
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement stat= con.createStatement();
			ResultSet rs =stat.executeQuery("SELECT name,lastname,id_user_type,inner_key FROM Employee WHERE inner_key="+String.valueOf(id));
			rs.next();
			Employee c=new Employee(rs.getString(1),rs.getString(2), rs.getInt(4), rs.getInt(3));
			stat.close();
			return c;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
		return null;
	}
	private Client getClient(int id){
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement stat= con.createStatement();
			ResultSet rs =stat.executeQuery("SELECT " +
					"CI," +//1
					"name," +//2
					"lastName," +//3
					"address," +//4
					"tel," +//5
					"cel," +//6
					"birthdate," +//7
					"registrationDate" +//8
					" FROM Client WHERE inner_key="+String.valueOf(id));
			rs.next();
			Client c=new Client(
					id,
					rs.getString(2),
					rs.getString(3),
					rs.getString(4),
					rs.getString(5),
					rs.getString(6),
					new Date(rs.getLong(8)),
					rs.getString(7));
			stat.close();
			return c;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
		return null;
	}
	private ArrayList<Order> getOrder(Sale compra){
		Statement st;
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			st=con.createStatement();
			String sql="SELECT inner_key,id_sale,especifications,is_ready FROM DataBaseProyectGiu.Order WHERE id_sale="+String.valueOf(compra.getSaleKey());
			ResultSet r=st.executeQuery(sql);
			
			ArrayList<Order> ordenes=new ArrayList<Order>();
			while(r.next()){
				ordenes.add(new Order(r.getInt(1), r.getBoolean(2),  getArticlulosDeOrden(r.getInt(1)), splitDescription(r.getString(3)),compra.getTableKey() ));
			} 
			
			st.close();
			////////////////////////hay que alterar el constructor de order
			return ordenes;
		} catch (SQLException e) {

			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
		
		
		return null;
	}
	private ArrayList<Article> getArticlulosDeOrden(int id_orden){
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement st=con.createStatement();
			ResultSet r=st.executeQuery("SELECT name,description,primary_key,price FROM Article JOIN Article_Order ON Article_Order.id_article=Article.inner_key  WHERE Article_Order.id_order="+String.valueOf(id_orden));
			ArrayList<Article> articulos=new ArrayList<Article>();
			 while(r.next()){
				 if (getCategory(r.getInt(3)).getPrimaryKey()==0){
					 articulos.add(new Combo(r.getString(1), r.getString(2), r.getInt(3), r.getDouble(4), getArticulosDeCombo(r.getInt(3))));
				 }else{
				 articulos.add(new Article(r.getString(1),
					  r.getString(2), r.getInt(3), r.getDouble(4),
					  getIngredients(r.getInt(3)),
					  getCategory(r.getInt(3))));
				 }
 }
			st.cancel();
			return articulos;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
		
		return null;
	}
	
	private ArrayList<Article> getArticulosDeCombo(int comboId){
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement st=con.createStatement();
			ResultSet r=st.executeQuery("SELECT name,description,primary_key,price FROM Article JOIN Article_Combo ON Article_Combo.id_atomizable_unit=Article.inner_key  WHERE Article_Combo.id_combo="+String.valueOf(comboId));
			ArrayList<Article> articulos=new ArrayList<Article>();
			 while(r.next()){
				 articulos.add(new Article(r.getString(1),
					  r.getString(2), r.getInt(3), r.getDouble(4),
					  getIngredients(r.getInt(3)),
					  getCategory(r.getInt(3))));
			 }
			st.cancel();
			return articulos;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
		
		return null;
	}
	
	private Ingredient[] getIngredients(int articleID) {
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement stat = con.createStatement();
			ResultSet rs = stat
					.executeQuery("SELECT Ingredient.name,Ingredient.in_stock,Ingredient.inner_key FROM Ingredient " +
							"JOIN Article_Ingredient ON Article_Ingredient.id_ingredient=Ingredient.inner_key " +
							"JOIN Atomizable_unit ON Atomizable_unit.inner_key=Article_Ingredient.id_atomizable_unit " +
							"JOIN Article ON Article.inner_key=Atomizable_unit.inner_key" +
							" WHERE Article.primary_key="
							+ String.valueOf(articleID));
			ArrayList<Ingredient> ingredientes = new ArrayList<Ingredient>();
			while (rs.next()) {
				ingredientes.add(new Ingredient(rs.getString(1), rs.getBoolean(2), rs.getInt(3)));
			}
			stat.close();
			Ingredient[] ingr = new Ingredient[ingredientes.size()];
			ingredientes.toArray(ingr);
			return ingr;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }

		return null;
	}
	private Category getCategory(int articleID) {
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement stat = con.createStatement();
			ResultSet rs = stat
					.executeQuery("SELECT Category.name,Category.primary_key FROM Category JOIN Article ON Article.id_category=Category.inner_key WHERE Article.primary_key="
							+ String.valueOf(articleID));
			rs.next();
			Category c = new Category(rs.getString(1), rs.getInt(2));
			stat.close();
			return c;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
		return null;

	}
	private ArrayList<String> splitDescription(String description){
		if (description==null) return null;
		String[] s=description.split("!OO!");
		ArrayList<String> als=new ArrayList<String>();
		Collections.addAll(als, s);
		return als;
	}
	private String joinDescriptions(ArrayList<String> description){
		String s="";
		for (String ss:description){
			s+=(ss!=null) ? ss+" !OO!" : " !OO!";
		}
		return s;
	}
	public void newSale(Sale e) throws BadClientException, BadEmployeeException {
		if (e.getClient()==null) e.setClient(ClientDAO.getInstance().getClient(0));
		if (e.getEmp()==null) e.setEmp(EmployeeDAO.getInstance().getEmployee(0));
		if (isNot(e.getClient())) throw new BadClientException(null);
		if (isNot(e.getEmp())) throw new BadEmployeeException();
		String sql="INSERT INTO Sale (table_key,id_employee,id_client,open_date,address,tel,total) VALUES (?,?,?,?,?,?,?)" ;
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			PreparedStatement st=con.prepareStatement(sql);
			if(e!=null){
				st.setInt(1, e.getTableKey());
				st.setInt(2, (e.getEmp()!=null) ? getEmployeeInnerKey(e.getEmp().getId()) : 1);
				st.setInt(3, (e.getClient()!=null) ? getClientInnerKey(e.getClient().getClientId()) : 0);
				st.setDate(4,  new java.sql.Date(e.getOpenDate().getTime()));
				st.setString(5, (e.getAddress()!=null) ? e.getAddress() : "");
				st.setString(6, (e.getTel()!=null) ? e.getTel() : "");
				st.setDouble(7, e.getTotal());
				st.execute();
				for(Order i:e.getOrders()){
					newOrder(i,null);
				}
			}
			
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException ex) {
					// TODO Auto-generated catch block
					ex.printStackTrace();
				}	
	  }
	}

	private int getEmployeeInnerKey(int id) {
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement st= con.createStatement();
			ResultSet r=st.executeQuery(
				  "SELECT inner_key FROM Employee WHERE active=1 AND CI="+String.valueOf(id));
				 
				  r.next();
				  
				  return r.getInt(1);
				  
				  } catch (SQLException e) { e.printStackTrace();
				  }finally{
						if(con!=null)
							try {
								con.close();
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}	
				  }
		return 0;
	}

	private Integer getClientInnerKey(int clientId) {
		
			Connection con=null;
			try {
				con=DAOHelper.getInstance().getConnection();
				Statement st= con.createStatement();
				ResultSet r=st.executeQuery(
					  "SELECT inner_key FROM Client WHERE active=1 AND CI="+String.valueOf(clientId));
					 
					  r.next();
					  
					  return r.getInt(1);
					  
					  } catch (SQLException e) { e.printStackTrace();
					  }finally{
							if(con!=null)
								try {
									con.close();
								} catch (SQLException e) {
									// TODO Auto-generated catch block
									e.printStackTrace();
								}	
					  }
			return 0;

	}

	private boolean isNot(Employee emp) {
		Employee c=EmployeeDAO.getInstance().getEmployee(emp.getId());
		if (c==null) return true;
		return false;
	}

	private void newOrder(Order o,Integer id_venta){
		
		String sql="SELECT inner_key FROM Sale WHERE inner_key=(SELECT max(inner_key)FROM Sale)";
		ResultSet r;		
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			if(id_venta==null){
				
			r = con.createStatement().executeQuery(sql);
			r.next();
			id_venta=r.getInt(1);
			}
			sql="Insert INTO DataBaseProyectGiu.Order (id_sale,especifications) VALUES ";
			sql=sql+"("+id_venta+",'"+joinDescriptions(o.getEspecif())+"')";
			con.createStatement().execute(sql);
			
			sql="SELECT inner_key FROM DataBaseProyectGiu.Order WHERE inner_key=(SELECT max(inner_key)FROM DataBaseProyectGiu.Order)";
			ResultSet rs=con.createStatement().executeQuery(sql);
			rs.next();
			int id_pedido=rs.getInt(1);
			rs.close();
			
			for(Article i:o.getArts()){
				ResultSet t=con.createStatement().executeQuery("SELECT inner_key FROM Article WHERE primary_key="+String.valueOf(i.getPrimaryKey()));
				t.next();
				int id_articulo=t.getInt(1);
				t.close();
				sql="INSERT INTO Article_Order (id_order,id_article) VALUES("+String.valueOf(id_pedido)+","+String.valueOf(id_articulo)+")";
				con.createStatement().execute(sql);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
	}
	
	public Sale[] getOpenTakeoOutSales(){//table key ==-2
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			String sql= "SELECT " +//
				  "inner_key," +//1
				  "id_employee," +//2
				  "id_client," +//3
				  "open_date," +//4
				  "close_date," +//5
				  "address," + //6
				  "tel," +//7
				  "is_open," +//8
				  "total," +//9
				  "table_key"+//11
				  " FROM Sale WHERE is_open=1 AND table_key=-2";
			ResultSet r=con.createStatement().executeQuery(sql);
				  ArrayList<Sale> articulos=new ArrayList<Sale>();
				  
				  while(r.next()){
					Sale s=new Sale(r.getInt(1),
							  r.getInt(10),
							  getEmployee(r.getInt(2)),
							  getClient(r.getInt(3)),
							  new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(r.getString(4)),
							  new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(r.getString(5)),
							  null,
							  r.getString(6),
							  r.getString(7),
							  r.getBoolean(8),
							  r.getDouble(9));
					  s.setOrders(getOrder(s));
					  articulos.add(s); }
				  
				  Sale[] ingr=new Sale[articulos.size()];
				  articulos.toArray(ingr);
				  return ingr; }
		catch (SQLException e) { e.printStackTrace();
				  } catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					if(con!=null)
						try {
							con.close();
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}	
			  }
		return null;
	}
	
	public Sale[] getOpenDeliverySales(){//table key ==-1
		Connection con=null;
		try { 
			con=DAOHelper.getInstance().getConnection();
			String sql= "SELECT " +//
				  "inner_key," +//1
				  "id_employee," +//2
				  "id_client," +//3
				  "open_date," +//4
				  "close_date," +//5
				  "address," + //6
				  "tel," +//7
				  "is_open," +//8
				  "total," +//9
				  "table_key"+//11
				  " FROM Sale WHERE is_open=1 AND table_key=-1";
			ResultSet r=con.createStatement().executeQuery(sql);
				  ArrayList<Sale> articulos=new ArrayList<Sale>();
				  
				  while(r.next()){
					Sale s=new Sale(r.getInt(1),
							  r.getInt(10),
							  getEmployee(r.getInt(2)),
							  getClient(r.getInt(3)),
							  new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(r.getString(4)),
							  new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(r.getString(5)),
							  null,
							  r.getString(6),
							  r.getString(7),
							  r.getBoolean(8),
							  r.getDouble(9));
					  s.setOrders(getOrder(s));
					  articulos.add(s); }
				  
				  Sale[] ingr=new Sale[articulos.size()];
				  articulos.toArray(ingr);
				  return ingr; }
		catch (SQLException e) { e.printStackTrace();
				  } catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					if(con!=null)
						try {
							con.close();
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}	
			  }
		return null;
	}
	
	public void updateSale(Sale sale){ //para agregar pedidos nuevos.
	
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			con.createStatement().execute("DELETE FROM DataBaseProyectGiu.Order WHERE id_sale="+sale.getSaleKey());
			for (Order i:sale.getOrders()){
				newOrder(i,sale.getSaleKey());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
	}

	public boolean hasOpenSale(int tableKey) {//
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			ResultSet s= con.createStatement().executeQuery("SELECT * FROM Sale WHERE table_key="+String.valueOf(tableKey)+" AND is_open=1");
			boolean respuesta=s.next();

			return respuesta;
			 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
		return false;
	}
	
	public Sale getSale(int tableKey) throws NotSaleException{//
		Connection con=null;
		try { 
			con=DAOHelper.getInstance().getConnection();
			String sql= "SELECT " +//
				  "inner_key," +//1
				  "id_employee," +//2
				  "id_client," +//3
				  "open_date," +//4
				  "close_date," +//5
				  "address," + //6
				  "tel," +//7
				  "is_open," +//8
				  "total," +//9
				  "table_key"+//11
				  " FROM Sale WHERE is_open=1 AND table_key="+tableKey;
			ResultSet r=con.createStatement().executeQuery(sql);
				  
				  r.next();
					Sale s=new Sale(r.getInt(1),
							  r.getInt(10),
							  getEmployee(r.getInt(2)),
							  getClient(r.getInt(3)),
							  new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(r.getString(4)),
							  new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(r.getString(5)),
							  null,
							  r.getString(6),
							  r.getString(7),
							  r.getBoolean(8),
							  r.getDouble(9));
					  s.setOrders(getOrder(s));
				  
				  return s; }
		catch (SQLException e) { e.printStackTrace();
				  } catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					if(con!=null)
						try {
							con.close();
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}	
			  }
		return null;
	}
	
	public Integer[] getOpenTables(){//
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			ResultSet r=con.createStatement().executeQuery("SELECT DISTINCT table_key FROM Sale WHERE is_open=1");
			ArrayList<Integer> a=new ArrayList<Integer>();
		
			while (r.next()){
				a.add(r.getInt(1));
			}
			Integer[] mesas=new Integer[a.size()];
			return a.toArray(mesas);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
		return null;
	}
	
	public void updateTotal(Sale sale){
		Connection con=null;
		String sql="UPDATE Sale SET ";
		sql=sql+"total="+String.valueOf(sale.getTotal())+" ";
		sql=sql+"WHERE inner_key="+String.valueOf(sale.getSaleKey());
		try {
			con=DAOHelper.getInstance().getConnection();
			con.createStatement().executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }

	};
	
	public void updateClient(Sale sale) throws BadClientException{	
		Connection con=null;
		if (isNot(sale.getClient())) throw new BadClientException(null);
		String sql="UPDATE Sale SET ";
		sql=sql+"id_client="+String.valueOf(sale.getClient().getClientId())+" ";
		sql=sql+"WHERE inner_key="+String.valueOf(sale.getSaleKey());
		try {
			con=DAOHelper.getInstance().getConnection();
			con.createStatement().executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }

	};
	
	public void updateEmployee(Sale sale) throws BadClientException{	
		Connection con=null;
		if (isNot(sale.getClient())) throw new BadClientException(null);
		String sql="UPDATE Sale SET ";
		sql=sql+"id_employee="+String.valueOf(sale.getClient().getClientId())+" ";
		sql=sql+"WHERE inner_key="+String.valueOf(sale.getSaleKey());
		try {
			con=DAOHelper.getInstance().getConnection();
			con.createStatement().executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }

	};
	
	private boolean isNot(Client client) {
		Client c=ClientDAO.getInstance().getClient(client.getClientId());
		if (c==null) return true;
		return false;
	}

	public void updateClose(Sale sale){

		String sql="UPDATE Sale SET ";
		sql=sql+"is_open=0 ";
		sql=sql+"WHERE inner_key="+String.valueOf(sale.getSaleKey());
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			con.createStatement().executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
	}; //---> pasarlo a False, nunca se puede al revez.

	public Order[] getOpenOrders(){
		String sql="SELECT DataBaseProyectGiu.Order.inner_key, DataBaseProyectGiu.Order.especifications, DataBaseProyectGiu.Order.id_sale, DataBaseProyectGiu.Order.is_ready, Sale.table_key FROM DataBaseProyectGiu.Order JOIN Sale ON DataBaseProyectGiu.Order.id_sale=Sale.inner_key WHERE DataBaseProyectGiu.Order.is_ready=1";
		Order[] o=new Order[0];
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			ArrayList<Order> a=new ArrayList<Order>();
			ResultSet sr=con.createStatement().executeQuery(sql);
			while(sr.next()){
				a.add(new Order(sr.getInt(1), false, getArticlulosDeOrden(sr.getInt(1)), splitDescription(sr.getString(2)), sr.getInt(5)));
			}
			Order[] bar = a.toArray(new Order[0]);
			return bar;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
		return o;
	}

	public Sale getSalePrimaryKey(int primaryKey) throws NotSaleException{
		Connection con=null;
		try { 
			con=DAOHelper.getInstance().getConnection();
			String sql= "SELECT " +//
				  "inner_key," +//1
				  "id_employee," +//2
				  "id_client," +//3
				  "open_date," +//4
				  "close_date," +//5
				  "address," + //6
				  "tel," +//7
				  "is_open," +//8
				  "total," +//9
				  "table_key"+//11
				  " FROM Sale WHERE inner_key="+primaryKey;
			ResultSet r=con.createStatement().executeQuery(sql);
				  
				  if(!r.next()) throw new NotSaleException(null);
					Sale s=new Sale(r.getInt(1),
							  r.getInt(10),
							  getEmployee(r.getInt(2)),
							  getClient(r.getInt(3)),
							  new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(r.getString(4)),
							  new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(r.getString(5)),
							  null,
							  r.getString(6),
							  r.getString(7),
							  r.getBoolean(8),
							  r.getDouble(9));
					  s.setOrders(getOrder(s));
				  
				  return s; }
		catch (SQLException e) { e.printStackTrace();
				  } catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					if(con!=null)
						try {
							con.close();
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}	
			  }
		return null;
	}
	
	public void setReady(Order ord){
		Connection con=null;
		
		try {
			con=DAOHelper.getInstance().getConnection();
			con.createStatement().execute("UPDATE order SET is_ready='1' WHERE inner_key="+ord.getPrimaryKey());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
		
	}
}